#!/bin/sh
# MySQL数据库完全备份
# 基于mysqldump
AppDir=/App/mysql
PATH=$AppDir/bin:$PATH
MailTo=dongsong@teacherclub.com.cn
Day=$(date '+%Y-%m-%d')
Time=$(date '+%Y-%m-%d %H:%M:%S')
SaveDays=30
User=root
Password=Root#123
BackupBase=/App/backup/mysql
BackupDir=$BackupBase/$Day
LogFile=$BackupDir/backup.log
MailFile=$BackupDir/mail.txt
HostName=$(hostname)
IPList=$(ifconfig | grep -v '127.0.0.1' | sed 's/addr://g' | grep 'inet ' | awk '{print $2}')

test -d $BackupDir || mkdir -p $BackupDir
find $BackupBase -maxdepth 1 -type d -mtime +$SaveDays -exec rm -rf {} \;

mysqlshow &> /dev/null
if [ "$?" -eq 0 ]; then
    Dump="mysqldump -R -q -l"
    Show="mysqlshow"
else
    Dump="mysqldump -R -q -l -u$User -p$Password"
    Show="mysqlshow -u$User -p$Password"
fi

DBList=$($Show | grep -Ev '(-|Database|information_schema|performance_schema|test)' | awk '{print $2'})

echo "##############################" > $MailFile
echo "$Time" >> $MailFile
echo "$HostName" >> $MailFile
for IP in $IPList
do
    echo "$IP" >> $MailFile
done

Number=0
Fail=0
cd $BackupDir
for DB in $DBList
do
    DBFile=$DB.sql
    CompDBFile=$DBFile.bz2
    Number=$(($Number+1))
    $Dump $DB > $DBFile
    if [ "$?" -eq 0 ]; then
        printf "%3d:$DB备份成功\n" $Number | tee -a $MailFile
        bzip2 -fk $DBFile
        if [ "$?" -eq 0 ]; then
            printf "    $DB压缩成功\n" | tee -a $MailFile
            rm -f $DBFile
        else
            printf "    $DB压缩失败\n" | tee -a $MailFile
            Fail=1
        fi
    else
        printf "%3d:$DB备份失败\n" $Number | tee -a $MailFile
        Fail=1
    fi
done
echo "##############################" >> $MailFile

cat $MailFile >> $LogFile
[ "$Fail" -eq 1 ] && cat $MailFile | mutt -s "MySQL数据库备份" $MailTo

